package com.example.demo.mapper;

import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.example.demo.entity.Borrow;
import com.example.demo.entity.dto.BorrowCountByMonth;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;

import java.time.LocalDateTime;
import java.util.List;
import java.util.Map;

@Mapper
public interface BorrowMapper extends BaseMapper<Borrow> {
    // 获取最近12个月的借阅数量
    @Select("SELECT DATE_FORMAT(borrow_date, '%Y-%m') AS month, COUNT(*) AS count FROM borrow GROUP BY month ORDER BY month ASC")
    List<BorrowCountByMonth> getBorrowCountByMonth();
    @Select("select count(*) from borrow where borrow_date between #{startTime} and #{endTime}")
    Integer countBetween(LocalDateTime startTime, LocalDateTime endTime);

    @Select("SELECT user_major, COUNT(*) AS count FROM borrow  GROUP BY user_major ORDER BY count DESC LIMIT 10")
    List<Map<String, Integer>> getBorrowedUsersByMajorWithParity();

    //查询从周一到周末每天的借阅数量使用map返回
    @Select("SELECT DATE_FORMAT(borrow_date, '%Y-%m-%d') AS borrowDate, COUNT(*) AS count " +
            "FROM borrow " +
            "WHERE borrow_date BETWEEN DATE_SUB(NOW(), INTERVAL 6 DAY) AND NOW() " +
            "GROUP BY borrowDate " +
            "ORDER BY borrowDate DESC")
    List<Map<String, Object>> countBorrowByWeek();

    @Select("SELECT COUNT(*) AS count FROM borrow WHERE user_grade = #{grade}")
    int selectGradeBorrowedCountByGrade(@Param("grade") int grade);

    @Select(" SELECT * FROM borrow ORDER BY borrow_date DESC LIMIT #{count}")
    List<Borrow> selectLatestBorrow(int i);
}


